package com.example.wangyongdong.bearsqlitedemo.db;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.wangyongdong.bearsqlitedemo.entity.MoneyEntity;
import com.example.wangyongdong.bearsqlitedemo.entity.RecordEntity;
import com.example.wangyongdong.bearsqlitedemo.Interface.TableRecordOperation;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by wangyongdong on 2017/4/14.
 */

public class RecordOperationAndroid implements TableRecordOperation{
    private SQLiteOpenHelper mHelper;

    public RecordOperationAndroid(SQLiteOpenHelper mHelper) {
        this.mHelper = mHelper;
    }

    @Override
    public void addRecord(RecordEntity record) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        if (record.get_id() != 0){
            values.put(BearSQLiteHelper.R_ID,record.get_id());
        }
        values.put(BearSQLiteHelper.DATE, record.getDate());
        values.put(BearSQLiteHelper.ODOMETER, record.getOdometer());
        values.put(BearSQLiteHelper.PRICE, record.getPrice());
        values.put(BearSQLiteHelper.YUAN, record.getYuan());
        values.put(BearSQLiteHelper.TYPE, record.getType());
        values.put(BearSQLiteHelper.GASSUP, record.getGassup());
        values.put(BearSQLiteHelper.REMARK, record.getRemark());
        values.put(BearSQLiteHelper.CARLD, record.getCarld());
        values.put(BearSQLiteHelper.FORGET, record.getForget());
        values.put(BearSQLiteHelper.LIGHTON, record.getLightOn());
        values.put(BearSQLiteHelper.STATIONLD, record.getStationld());
        db.insert(BearSQLiteHelper.RECORDS_TBL, null, values);
        db.close();
    }

    @Override
    public void removeRecord(int id) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String whereClause = BearSQLiteHelper.R_ID + "= ?";
        String[] whereArgs = new String[]{String.valueOf(id)};
        db.delete(BearSQLiteHelper.RECORDS_TBL,whereClause,whereArgs);
        db.close();
    }

    @Override
    public void updateRecord(RecordEntity record) {
        updateRecord(openDatabase(),true,record);
    }

    private void updateRecord(SQLiteDatabase db,boolean isClose,RecordEntity record){
        ContentValues values = new ContentValues();
        values.put(BearSQLiteHelper.DATE,record.getDate());
        values.put(BearSQLiteHelper.ODOMETER,record.getOdometer());
        values.put(BearSQLiteHelper.PRICE,record.getPrice());
        values.put(BearSQLiteHelper.YUAN,record.getYuan());
        values.put(BearSQLiteHelper.TYPE,record.getType());
        values.put(BearSQLiteHelper.GASSUP,record.getGassup());
        values.put(BearSQLiteHelper.REMARK,record.getRemark());
        values.put(BearSQLiteHelper.CARLD,record.getCarld());
        values.put(BearSQLiteHelper.FORGET,record.getForget());
        values.put(BearSQLiteHelper.LIGHTON,record.getLightOn());
        values.put(BearSQLiteHelper.STATIONLD,record.getStationld());
        String whereClause = BearSQLiteHelper.R_ID + " = ?";
        String[] whereArgs = new String[]{String.valueOf(record.get_id())};
        db.update(BearSQLiteHelper.RECORDS_TBL,values,whereClause,whereArgs);
        if (isClose){
            closeDatabase(db);
        }
    }

    @Override
    public List<RecordEntity> queryCarAllRecord() {
        List<RecordEntity> records = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql =  "SELECT A.*\n" +
                "FROM records_tbl as " +
                "" +
                ", cars_tbl AS B\n" +
                "where\n" +
                "A.carId = B.\"_id\"\n" +
                "AND\n" +
                "B.selected = 1;";
        Cursor cursor = db.rawQuery(sql,null);
        if (cursor != null && cursor.moveToFirst()){
            int indexId = cursor.getColumnIndex(BearSQLiteHelper.R_ID);
            int indexDate = cursor.getColumnIndex(BearSQLiteHelper.DATE);
            int indexOdometer = cursor.getColumnIndex(BearSQLiteHelper.ODOMETER);
            int indexPrice = cursor.getColumnIndex(BearSQLiteHelper.PRICE);
            int indexYuan = cursor.getColumnIndex(BearSQLiteHelper.YUAN);
            int indexType = cursor.getColumnIndex(BearSQLiteHelper.TYPE);
            int indexGassup = cursor.getColumnIndex(BearSQLiteHelper.GASSUP);
            int indexRemark = cursor.getColumnIndex(BearSQLiteHelper.REMARK);
            int indexCarld = cursor.getColumnIndex(BearSQLiteHelper.CARLD);
            int indexForget = cursor.getColumnIndex(BearSQLiteHelper.FORGET);
            int indexLightOn = cursor.getColumnIndex(BearSQLiteHelper.LIGHTON);
            int indexStationld = cursor.getColumnIndex(BearSQLiteHelper.STATIONLD);
            do {
                int id = cursor.getInt(indexId);
                String date = cursor.getString(indexDate);
                String odometer = cursor.getString(indexOdometer);
                String price = cursor.getString(indexPrice);
                String yuan = cursor.getString(indexYuan);
                String type = cursor.getString(indexType);
                String gassup = cursor.getString(indexGassup);
                String remark = cursor.getString(indexRemark);
                String carld = cursor.getString(indexCarld);
                String forget = cursor.getString(indexForget);
                String lightOn = cursor.getString(indexLightOn);
                String stationld = cursor.getString(indexStationld);
                RecordEntity record = new RecordEntity(id);
                record.setDate(date);
                record.setOdometer(odometer);
                record.setPrice(price);
                record.setYuan(yuan);
                record.setType(type);
                record.setGassup(gassup);
                record.setRemark(remark);
                record.setCarld(carld);
                record.setForget(forget);
                record.setLightOn(lightOn);
                record.setStationld(stationld);
                records.add(record);
            }while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return records;
    }

    @Override
    public List<RecordEntity> queryCarYearRecord() {
        List<RecordEntity> records = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "SELECT A.date, A.odometer, A.price, A.yuan, A.carId, datetime(A.date / 1000, 'unixepoch')\n" +
                "from records_tbl as A, cars_tbl as B\n" +
                "WHERE A.date > (\n" +
                "    strftime('%s', datetime('now', '-12 month')) * 1000\n" +
                ")\n" +
                "AND\n" +
                "A.carId = B.\"_id\"\n" +
                "AND\n" +
                "B.selected = 1;";
        Cursor cursor = db.rawQuery(sql,null);
        if (cursor != null && cursor.moveToFirst()){
            int indexId = cursor.getColumnIndex(BearSQLiteHelper.R_ID);
            int indexData = cursor.getColumnIndex(BearSQLiteHelper.DATE);
            int indexOdometer = cursor.getColumnIndex(BearSQLiteHelper.ODOMETER);
            int indexPrice = cursor.getColumnIndex(BearSQLiteHelper.PRICE);
            int indexYuan = cursor.getColumnIndex(BearSQLiteHelper.YUAN);
            int indexType = cursor.getColumnIndex(BearSQLiteHelper.TYPE);
            int indexGassup = cursor.getColumnIndex(BearSQLiteHelper.GASSUP);
            int indexRemark = cursor.getColumnIndex(BearSQLiteHelper.REMARK);
            int indexCarld = cursor.getColumnIndex(BearSQLiteHelper.CARLD);
            int indexForget = cursor.getColumnIndex(BearSQLiteHelper.FORGET);
            int indexLightOn = cursor.getColumnIndex(BearSQLiteHelper.LIGHTON);
            int indexStationld = cursor.getColumnIndex(BearSQLiteHelper.STATIONLD);
            do {
                int id = cursor.getInt(indexId);
                String data = cursor.getString(indexData);
                String odometer = cursor.getString(indexOdometer);
                String price = cursor.getString(indexPrice);
                String yuan = cursor.getString(indexYuan);
                String type = cursor.getString(indexType);
                String gassup = cursor.getString(indexGassup);
                String remark = cursor.getString(indexRemark);
                String carld = cursor.getString(indexCarld);
                String forget = cursor.getString(indexForget);
                String lightOn = cursor.getString(indexLightOn);
                String stantionld = cursor.getString(indexStationld);
                RecordEntity record = new RecordEntity(id);
                record.setDate(data);
                record.setCarld(carld);
                record.setForget(forget);
                record.setGassup(gassup);
                record.setLightOn(lightOn);
                record.setOdometer(odometer);
                record.setPrice(price);
                record.setYuan(yuan);
                record.setRemark(remark);
                record.setStationld(stantionld);
                record.setType(type);
                records.add(record);
            } while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return records;
    }

    @Override
    public List<RecordEntity> queryCarHalfYearRecord() {
        List<RecordEntity> records = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "SELECT A.date, A.odometer, A.price, A.yuan, A.carId, datetime(A.date / 1000, 'unixepoch')\n" +
                "from records_tbl as A, cars_tbl as B\n" +
                "WHERE A.date > (\n" +
                "    strftime('%s', datetime('now', '-6 month')) * 1000\n" +
                ")\n" +
                "AND\n" +
                "A.carId = B.\"_id\"\n" +
                "AND\n" +
                "B.selected = 1;";
        Cursor cursor = db.rawQuery(sql,null);
        if (cursor != null && cursor.moveToFirst()){
            int indexId = cursor.getColumnIndex(BearSQLiteHelper.R_ID);
            int indexData = cursor.getColumnIndex(BearSQLiteHelper.DATE);
            int indexOdometer = cursor.getColumnIndex(BearSQLiteHelper.ODOMETER);
            int indexPrice = cursor.getColumnIndex(BearSQLiteHelper.PRICE);
            int indexYuan = cursor.getColumnIndex(BearSQLiteHelper.YUAN);
            int indexType = cursor.getColumnIndex(BearSQLiteHelper.TYPE);
            int indexGassup = cursor.getColumnIndex(BearSQLiteHelper.GASSUP);
            int indexRemark = cursor.getColumnIndex(BearSQLiteHelper.REMARK);
            int indexCarld = cursor.getColumnIndex(BearSQLiteHelper.CARLD);
            int indexForget = cursor.getColumnIndex(BearSQLiteHelper.FORGET);
            int indexLightOn = cursor.getColumnIndex(BearSQLiteHelper.LIGHTON);
            int indexStationld = cursor.getColumnIndex(BearSQLiteHelper.STATIONLD);
            do {
                int id = cursor.getInt(indexId);
                String data = cursor.getString(indexData);
                String odometer = cursor.getString(indexOdometer);
                String price = cursor.getString(indexPrice);
                String yuan = cursor.getString(indexYuan);
                String type = cursor.getString(indexType);
                String gassup = cursor.getString(indexGassup);
                String remark = cursor.getString(indexRemark);
                String carld = cursor.getString(indexCarld);
                String forget = cursor.getString(indexForget);
                String lightOn = cursor.getString(indexLightOn);
                String stationld = cursor.getString(indexStationld);
                RecordEntity record = new RecordEntity(id);
                record.setDate(data);
                record.setCarld(carld);
                record.setForget(forget);
                record.setGassup(gassup);
                record.setLightOn(lightOn);
                record.setOdometer(odometer);
                record.setPrice(price);
                record.setType(type);
                record.setYuan(yuan);
                record.setRemark(remark);
                record.setStationld(stationld);
                records.add(record);
            }while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return records;
    }

    @Override
    public List<RecordEntity> queryCarThreeMonthRecord() {
        List<RecordEntity> records = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "SELECT A.date, A.odometer, A.price, A.yuan, A.carId, datetime(A.date / 1000, 'unixepoch')\n" +
                "from records_tbl as A, cars_tbl as B\n" +
                "WHERE A.date > (\n" +
                "    strftime('%s', datetime('now', '-3 month')) * 1000\n" +
                ")\n" +
                "AND\n" +
                "A.carId = B.\"_id\"\n" +
                "AND\n" +
                "B.selected = 1;";
        Cursor cursor = db.rawQuery(sql,null);
        if (cursor != null && cursor.moveToFirst()){
            int indexId = cursor.getColumnIndex(BearSQLiteHelper.R_ID);
            int indexData = cursor.getColumnIndex(BearSQLiteHelper.DATE);
            int indexOdometer = cursor.getColumnIndex(BearSQLiteHelper.ODOMETER);
            int indexPrice = cursor.getColumnIndex(BearSQLiteHelper.PRICE);
            int indexYuan = cursor.getColumnIndex(BearSQLiteHelper.YUAN);
            int indexType = cursor.getColumnIndex(BearSQLiteHelper.TYPE);
            int indexGassup = cursor.getColumnIndex(BearSQLiteHelper.GASSUP);
            int indexRemark = cursor.getColumnIndex(BearSQLiteHelper.REMARK);
            int indexCarld = cursor.getColumnIndex(BearSQLiteHelper.CARLD);
            int indexForget = cursor.getColumnIndex(BearSQLiteHelper.FORGET);
            int indexLightOn = cursor.getColumnIndex(BearSQLiteHelper.LIGHTON);
            int indexStationld = cursor.getColumnIndex(BearSQLiteHelper.STATIONLD);
            do {
                int id = cursor.getInt(indexId);
                String data = cursor.getString(indexData);
                String odometer = cursor.getString(indexOdometer);
                String price = cursor.getString(indexPrice);
                String yuan = cursor.getString(indexYuan);
                String type = cursor.getString(indexType);
                String gassup = cursor.getString(indexGassup);
                String remark = cursor.getString(indexRemark);
                String carld = cursor.getString(indexCarld);
                String forget = cursor.getString(indexForget);
                String lightOn = cursor.getString(indexLightOn);
                String stationld = cursor.getString(indexStationld);
                RecordEntity record = new RecordEntity(id);
                record.setDate(data);
                record.setCarld(carld);
                record.setForget(forget);
                record.setGassup(gassup);
                record.setLightOn(lightOn);
                record.setOdometer(odometer);
                record.setPrice(price);
                record.setType(type);
                record.setYuan(yuan);
                record.setRemark(remark);
                record.setStationld(stationld);
                records.add(record);
            }while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return records;
    }

    @Override
    public List<MoneyEntity> queryEachYearMoney() {
        List<MoneyEntity> moneys = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "select" +
                " strftime('%Y',datetime(A.date / 1000, 'unixepoch'),'localtime') time," +
                " sum(A.yuan) money" +
                " from records_tbl as A, cars_tbl as B" +
                " where A.carId = B._id and B.selected = 1" +
                " GROUP BY time" +
                " ORDER BY time;";
        Cursor cursor = db.rawQuery(sql,null);
        if (cursor != null && cursor.moveToFirst()){
            int indexTime = cursor.getColumnIndex("time");
            int indexMoney = cursor.getColumnIndex("money");
            do {
                String time = cursor.getString(indexTime);
                float money = cursor.getFloat(indexMoney);
                MoneyEntity moneyEntity = new MoneyEntity();
                moneyEntity.setTime(time);
                moneyEntity.setMoney(money);
                moneys.add(moneyEntity);
            }while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return moneys;
    }

    @Override
    public List<MoneyEntity> queryEachMonthMoney() {
        List<MoneyEntity> moneys = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "select" +
                " strftime('%Y-%m',datetime(A.date / 1000, 'unixepoch'),'localtime') time," +
                " sum(A.yuan) money" +
                " from records_tbl as A, cars_tbl as B" +
                " where A.carId = B._id and B.selected = 1" +
                " GROUP BY time" +
                " ORDER BY time;";
        Cursor cursor = db.rawQuery(sql,null);
        if (cursor != null && cursor.moveToFirst()){
            int indexTime = cursor.getColumnIndex("time");
            int indexMoney = cursor.getColumnIndex("money");
            do {
                String time = cursor.getString(indexTime);
                float money = cursor.getFloat(indexMoney);
                MoneyEntity moneyEntity = new MoneyEntity();
                moneyEntity.setTime(time);
                moneyEntity.setMoney(money);
                moneys.add(moneyEntity);
            }while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return moneys;
    }

    private SQLiteDatabase openDatabase(){
        return mHelper.getWritableDatabase();
    }

    private void closeDatabase(SQLiteDatabase db){
        if (db != null){
            db.close();
        }
    }

}
